﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ReporteCompras')
	BEGIN
		DROP  Procedure  ReporteCompras
	END

GO

CREATE Procedure ReporteCompras

	(
		@FechaDesde datetime,
		@FechaHasta datetime,
		@Proveedor int = NULL,
		@Documento int = NULL,
		@Serie nchar(4) = NULL,
		@Numero int = NULL,
		@Personal int = NULL,
		@Estado char(1) = NULL
	)

AS
BEGIN

SELECT     
C.compraID AS ID, 
P.razonSocial AS Proveedor, 
CASE
	WHEN C.tipoDocumento = 0 THEN 'Boleta'
	WHEN C.tipoDocumento = 1 THEN 'Factura'
END AS Documento, 
P.RUC,
C.serieDocumento AS Serie, 
C.numeroDocumento AS Numero, 
C.fechaEmision AS Fecha, 
E.apellidos + ' ' + E.nombres AS Empleado, 
A.nombre AS Almacen, 
L.numero AS Lote, 
I.nombre AS Insumo, 
DCA.cantidad AS Cantidad, 
DC.precioUnitario AS Precio, 
C.estado AS Estado,
DCA.cantidad * DC.precioUnitario AS Total
FROM Compras AS C
INNER JOIN dbo.DetallesCompras AS DC ON C.compraID = DC.compraID 
INNER JOIN dbo.DetCompraAlmacenamiento AS DCA ON DC.compraID = DCA.compraID AND DC.loteID = DCA.loteID 
INNER JOIN dbo.Proveedores AS P ON C.proveedorID = P.proveedorID 
INNER JOIN dbo.Lotes AS L ON DC.loteID = L.loteID 
INNER JOIN dbo.Insumos AS I ON L.insumoID = I.insumoID 
INNER JOIN dbo.Personal AS E ON C.personalID = E.personalID 
INNER JOIN dbo.Almacenes AS A ON DCA.almacenID = A.almacenID
WHERE (C.fechaEmision >= @FechaDesde AND C.fechaEmision <= @FechaHasta) AND
CASE
	WHEN @Proveedor IS NULL THEN 1
	WHEN @Proveedor = P.proveedorID THEN 1
	ELSE 0
END = 1 AND
CASE
	WHEN @Documento IS NULL THEN 1
	WHEN @Documento = CONVERT(int, C.tipoDocumento) THEN 1
	ELSE 0	
END = 1 AND
CASE 
	WHEN ISNULL(@Serie, '') = '' THEN 1
	WHEN @Serie = C.serieDocumento THEN 1
	ELSE 0
END = 1 AND
CASE 
	WHEN @Numero IS NULL THEN 1
	WHEN @Numero = C.numeroDocumento THEN 1
	ELSE 0
END = 1 AND
CASE
	WHEN @Personal IS NULL THEN 1
	WHEN @Personal = C.personalID THEN 1
	ELSE 0
END = 1 AND
CASE
	WHEN ISNULL(@Estado, '') = '' THEN 1
	WHEN @Estado = C.estado THEN 1
	ELSE 0
END = 1

END

GO


GRANT EXEC ON ReporteCompras TO PUBLIC

GO


